﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IDAL;
using Model;
using System.Configuration;
using System.Data.SqlClient;

namespace DAL
{
    public class OrderDAL : IOrderDAL
    {
        public static string Conns = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        #region
        /// <summary>
        /// 销售订单显示
        /// </summary>
        /// <returns></returns>
        public List<OrderModel> OrderShow()
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = "select OrderID,UserTel,Delivery_address,Distribution_form,Paymethod,Total_amount,CONVERT(varchar(100),Place_Time,23) as Place_Time,(case when OrderState=0 then '正在进行' when OrderState=1 then '正在配送' when OrderState=2 then '已完成' end) as OrderState from ( select *, row_number() over (partition by OrderID order by OrderID) as group_idx from OrderTable) s where s.group_idx = 1";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<OrderModel> list = new List<OrderModel>();
                while (read.Read())
                {
                    OrderModel mo = new OrderModel();
                    mo.OrderID = read["OrderID"].ToString();
                    mo.UserTel = read["UserTel"].ToString();
                    mo.Distribution_form = read["Distribution_form"].ToString();
                    mo.Delivery_address = read["Delivery_address"].ToString();
                    mo.Paymethod = read["Paymethod"].ToString();
                    mo.Total_amount = Convert.ToInt32(read["Total_amount"]);
                    mo.Place_Time = read["Place_Time"].ToString();
                    mo.OrderState = read["OrderState"].ToString();
                    list.Add(mo);
                }
                conn.Close();
                return list;
            }
        }
        /// <summary>
        /// 根据订单来查询购买数据
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<OrderModel> SelectShow(string name)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = string.Format("select OrderID,FlowersName,purchaseNumber,FlowersImg,Sales_unit_price from OrderTable where OrderID='{0}'", name);
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<OrderModel> list = new List<OrderModel>();
                while (read.Read())
                {
                    OrderModel mo = new OrderModel();
                    mo.OrderID = read["OrderID"].ToString();
                    mo.FlowersName = read["FlowersName"].ToString();
                    mo.purchaseNumber = Convert.ToInt32(read["purchaseNumber"]);
                    mo.FlowersImg = read["FlowersImg"].ToString();
                    mo.Sales_unit_price = Convert.ToInt32(read["Sales_unit_price"]);
                    list.Add(mo);
                }
                conn.Close();
                return list;
            }
        }

        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int OrderUpt(string name)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = string.Format("update OrderTable set OrderState=1 where OrderID='{0}'", name);
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }


        /// <summary>
        /// 根据下拉框来查询数据
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<OrderModel> SelectLike(string name)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = string.Format("select OrderID,UserTel,Delivery_address,Distribution_form,Paymethod,Total_amount,CONVERT(varchar(100),Place_Time,23) as Place_Time,(case when OrderState=0 then '正在进行' when OrderState=1 then '正在配送' when OrderState=2 then '已完成' end) as OrderState from ( select *, row_number() over (partition by OrderID order by OrderID) as group_idx from OrderTable) s where s.group_idx = 1 and Paymethod ='{0}'", name);
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<OrderModel> list = new List<OrderModel>();
                while (read.Read())
                {
                    OrderModel mo = new OrderModel();
                    mo.OrderID = read["OrderID"].ToString();
                    mo.UserTel = read["UserTel"].ToString();
                    mo.Distribution_form = read["Distribution_form"].ToString();
                    mo.Delivery_address = read["Delivery_address"].ToString();
                    mo.Paymethod = read["Paymethod"].ToString();
                    mo.Total_amount = Convert.ToInt32(read["Total_amount"]);
                    mo.Place_Time = read["Place_Time"].ToString();
                    mo.OrderState = read["OrderState"].ToString();
                    list.Add(mo);
                }
                conn.Close();
                return list;
            }
        }


        /// <summary>
        /// 完成状态
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int OrderFull(string name)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sql = string.Format("update OrderTable set OrderState=2 where OrderID='{0}'", name);
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }
        #endregion
        #region//废除的方法
        /// <summary>
        /// 添加订单
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public int AddOrder(OrderModel order)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                conn.Open();
                string sqlstr = "insert into OrderTable(UserTel,Delivery_address,Distribution_form,Paymethod,Total_amount,Place_Time,OrderState,CommodityID,FlowersName,purchaseNumber,Sales_unit_price) values()";
            }
            return 1;
        }
        #endregion
        /// <summary>
        /// 添加订单
        /// </summary>
        /// <param name="name">花卉名称</param>
        /// <param name="price">单价</param>
        /// <param name="ids">花卉编号</param>
        /// <param name="numbers">数量</param>
        /// <param name="total">总价</param>
        /// <param name="address">配送地址</param>
        /// <param name="tell">联系人电话</param>
        /// <param name="method">配送方式</param>
        /// <param name="paymethod">支付方式</param>
        /// <param name="zong">总金额</param>
        /// <param name="url">图片地址</param>
        /// <returns></returns>
        public int AddOrder(string[] name, string[] price, string[] ids, string[] numbers, string[] total, string address, string tell, string method, string paymethod, int zong,string[] url)
        {
            using (SqlConnection conn = new SqlConnection(Conns))
            {
                string Placetime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                string orderID = DateTime.Now.ToString("yyyyMMddHHmmss");
                conn.Open();
                string sqlstr = "";
                for (var i = 0; i < name.Length; i++)
                {
                    sqlstr += "insert into OrderTable(UserTel,Delivery_address,Distribution_form,Paymethod,Total_amount,Place_Time,OrderState,CommodityID,FlowersName,purchaseNumber,Sales_unit_price,OrderID,FlowersImg) values('" + tell + "','" + address + "','" + method + "','" + paymethod + "'," + Convert.ToInt32(zong) + ",'" + Placetime + "',0,'" + ids[i] + "','" + name[i] + "'," + Convert.ToInt32(numbers[i]) + "," + Convert.ToInt32(price[i]) + ",'" + orderID + "','" + url[i] + "');delete ShoppingTable where ShopID=" + ids[i] + ";update FlowersTable set FlowersStock=FlowersStock-"+numbers[i]+" where FlowersName='"+name[i]+"';";
                }
                sqlstr = sqlstr.Substring(0, sqlstr.Length - 1);
                
                SqlCommand cmd = new SqlCommand(sqlstr,conn);
                return cmd.ExecuteNonQuery();

            }
        }
    }
}
